﻿'Clinciu Andrei
'Bram Debouvere
Option Strict On

Imports System.Collections.Generic
Imports System.Data.OleDb
Imports Microsoft.Office.Interop
Public Class Product
    Private _naam, _omschrijving, _afbeelding As String
    Private _prijs As Decimal
    Private _id As Integer
    Private _onbeschikbaar As Boolean
    Private _occasions As List(Of Occasion)

    Public Sub New(ByVal id As Integer, ByVal naam As String, ByVal omschrijving As String, ByVal afbeelding As String, ByVal prijs As Decimal, ByVal onbeschikbaar As Boolean, Optional ByVal occasions As List(Of Occasion) = Nothing)
        _id = id
        _naam = naam
        _omschrijving = omschrijving
        _afbeelding = afbeelding
        _onbeschikbaar = onbeschikbaar
        _prijs = prijs
        _occasions = occasions
    End Sub


    Public ReadOnly Property Id As Integer
        Get
            Return _id
        End Get
    End Property


    Public Property Naam As String
        Get
            Return _naam
        End Get
        Set(ByVal Value As String)
            _naam = Value
        End Set
    End Property

    Public Property Occasions As List(Of Occasion)
        Get
            Return _occasions
        End Get
        Set(ByVal Value As List(Of Occasion))
            _occasions = Value
        End Set
    End Property
    Public Property Omschrijving As String
        Get
            Return _omschrijving
        End Get
        Set(ByVal Value As String)
            _omschrijving = Value
        End Set
    End Property

    Public Property Afbeelding As String
        Get
            Return _afbeelding
        End Get
        Set(ByVal Value As String)
            _afbeelding = Value
        End Set
    End Property


    Public Property Onbeschikbaar As Boolean
        Get
            Return _onbeschikbaar
        End Get
        Set(ByVal Value As Boolean)
            _onbeschikbaar = Value
        End Set
    End Property

    Public Property Prijs As Decimal
        Get
            Return _prijs
        End Get
        Set(ByVal Value As Decimal)
            _prijs = Value
        End Set
    End Property

    Public Shared Function GetProductsFromDatabase(ByRef dbh As DatabaseHelper) As List(Of Product)
        Dim list As New List(Of Product)

        Dim reader As OleDbDataReader = dbh.GetReader("SELECT * FROM Product", {})
        Do While reader.Read()
            Dim product As New Product(CInt(reader("ProdId")), CStr(reader("ProdNaam")), CStr(reader("ProdOmschrijving")), CStr(reader("ProdAfbeelding")), CDec(reader("ProdPrijs")), CBool(reader("ProdOnbeschikbaar")))
            list.Add(product)
        Loop
        Return list
    End Function

    Public Shared Function GetProductsByOccasion(ByVal Occasion As Occasion, ByRef dbh As DatabaseHelper) As List(Of Product)
        Dim list As New List(Of Product)

        Dim reader As OleDbDataReader = dbh.GetReader("SELECT * FROM Product,GelegenheidProduct WHERE GPProdId = ProdId AND GPGelegenheidID = ?", {Occasion.Id})
        Do While reader.Read()
            Dim product As New Product(CInt(reader("ProdId")), CStr(reader("ProdNaam")), CStr(reader("ProdOmschrijving")), CStr(reader("ProdAfbeelding")), CDec(reader("ProdPrijs")), CBool(reader("ProdOnbeschikbaar")))
            list.Add(product)
        Loop
        Return list
    End Function

    'this uses a transaction
    Public Shared Sub AddProductToDb(ByVal product As Product, ByVal occasions As List(Of Occasion), ByRef db As DatabaseHelper)
        Dim transaction As OleDbTransaction = db.DBConnection.BeginTransaction()
        Dim prodId As Integer

        Dim price As Decimal = product.Prijs
    
        Try
            db.ExecuteSQL("INSERT INTO Product (ProdNaam,ProdOmschrijving,ProdAfbeelding,ProdPrijs,ProdOnbeschikbaar) VALUES (?,?,?,?,?)",
                           {product.Naam, product.Omschrijving, product.Afbeelding, price, product.Onbeschikbaar}, transaction)
            'get the id from the last insert with that name (yes i know more products can have the same name but we'll think it has only one)
            prodId = CInt(db.ExecuteScalar("select ProdId from Product WHERE  ProdNaam = ?", {product.Naam}, transaction))
            'add each insert in the transaction..
            For Each occ As Occasion In occasions
                db.ExecuteSQL("INSERT INTO GelegenheidProduct VALUES(?, ? )", {occ.Id, prodId}, transaction)
            Next

            transaction.Commit()
        Catch ex As Exception
            transaction.Rollback()
        End Try
    End Sub

    Public Shared Sub ChangeProduct(ByVal product As Product, ByVal occasions As List(Of Occasion), ByRef db As DatabaseHelper)
        db.ExecuteSQL("UPDATE Product SET ProdNaam = ?, ProdOmschrijving = ? , ProdAfbeelding = ?, ProdPrijs = ?, ProdOnbeschikbaar = ? WHERE ProdId =  ?",
                       {product.Naam, product.Omschrijving, product.Afbeelding, product.Prijs, product.Onbeschikbaar, product.Id}, True)

    End Sub
    'check if the name exists in the db
    Public Shared Function ExistsName(ByVal name As String, ByVal db As DatabaseHelper) As Boolean
        Dim reader As OleDbDataReader = db.GetReader("select ProdNaam from Product WHERE  ProdNaam = ?", {name})
        If reader.Read() Then
            If name.ToLower.Equals(reader("ProdNaam").ToString.ToLower) Then
                reader.Close()
                Return True
            End If
        End If
        reader.Close()
        Return False
    End Function
    Public Shared Function GetGelegenheidForProduct(ByVal product As Product, ByRef dbh As DatabaseHelper) As List(Of Integer)
        Dim theList As New List(Of Integer)
        Dim reader As OleDbDataReader = dbh.GetReader("SELECT GPGelegenheidID FROM GelegenheidProduct WHERE GPProdId = ? ", {product.Id})

        Do While reader.Read()
            theList.Add(CInt(reader("GPGelegenheidID")))
        Loop
        Return theList

    End Function

    Public Shared Function CanDelete(ByVal product As Product, ByRef db As DatabaseHelper) As Boolean
        Dim isDeletable As Boolean = False
        ' we usually get one or nothing.. so delete it
        ' Dim reader As OleDbDataReader = db.GetReader("select BestBetaald from Bestelling b,BestellingGeschenklijstProduct bgp where b.BestId = bgp.BGLPBestId and bgp.BGLPProdId = ?", {product.Id})
        Dim reader As OleDbDataReader = db.GetReader("select * from GeschenklijstProduct gp where gp.GLPProdId = ?", {product.Id})

        If reader.Read() Then
            isDeletable = True
        End If

        reader.Close()
        Return isDeletable
    End Function

    Public Shared Sub DeleteProduct(ByVal product As Product, ByRef db As DatabaseHelper)
        'delete the product
        '  Dim db As New DatabaseHelper
        'bo.GelegenheidProduct", column 'GPProdId'.
        db.ExecuteSQL("DELETE FROM GelegenheidProduct WHERE GPProdId =  ?", {product.Id}, True)
        db.ExecuteSQL("DELETE FROM Product WHERE ProdId =  ?", {product.Id}, True)
    End Sub

    Public Shared Function GetProductById(ByVal Id As Integer, ByRef dbh As DatabaseHelper) As Product
        Dim reader As OleDbDataReader = dbh.GetReader("SELECT * FROM Product WHERE ProdId = ?", {Id})
        reader.Read()
        Dim product As New Product(CInt(reader("ProdId")), CStr(reader("ProdNaam")), CStr(reader("ProdOmschrijving")), CStr(reader("ProdAfbeelding")), CDec(reader("ProdPrijs")), CBool(reader("ProdOnbeschikbaar")))
        reader.Close()
        Return product
    End Function


    Public Shared Sub ExportToExcel(ByVal db As DatabaseHelper, ByVal occ As Occasion, ByVal file As String)
        Dim theList As List(Of Product) = Product.GetProductsByOccasion(occ, db)

        If System.IO.Path.GetExtension(file) = "" Then
            file += ".xlsx"
        End If

        Dim ExcelApp As Excel.Application
        ExcelApp = New Excel.Application
        'don't show excel
        ExcelApp.Visible = False
        'Don't display allerts or errors
        ExcelApp.DisplayAlerts = False

        'new workbook
        Dim workbook As Excel.Workbook = ExcelApp.Workbooks.Add

        'variable for worksheet
        Dim worksheet As Excel.Worksheet

        'delete all worksheet except the first one
        Do While ExcelApp.Worksheets.Count > 1
            worksheet = CType(ExcelApp.Worksheets.Item(ExcelApp.Worksheets.Count), Excel.Worksheet)
            worksheet.Delete()
        Loop

        'het eerste werkblad selecteren
        worksheet = CType(ExcelApp.Worksheets.Item(1), Excel.Worksheet)
        worksheet.Name = "Producten"


        'Fill titles
        '    Bij producten wordt een export voorzien naar een Excel-lijst. De export mag enkel producten bevatten voor één specifieke gelegenheid.
        '      De export bevat product nummer, naam en beschrijving.
        '     Daarnaast worden nog 2 vrije velden toegevoegd voor het invullen van aantallen en categoriën.
        worksheet.Range("A1").Value = "Lijst met producten van Gelegenheid " + occ.Name
        worksheet.Range("A2").Value = "Product Nummer"
        worksheet.Range("B2").Value = "Product naam"
        worksheet.Range("C2").Value = "Beschrijving"
        worksheet.Range("D2").Value = "Aantallen"
        worksheet.Range("E2").Value = "Categorieën"

        'Font settings
        worksheet.Range("A2:E2").Font.Bold = True
        worksheet.Range("A1").Font.Size = 22

        Dim rng As Excel.Range = worksheet.Range("A3")
        For Each item As Product In theList
            rng.Value = item.Id
            rng.Offset(0, 1).Value = item.Naam
            rng.Offset(0, 2).Value = item.Omschrijving

            rng = rng.Offset(1, 0)
        Next

        'document opslaan
        workbook.SaveAs(file)
        ExcelApp.DisplayAlerts = True

        'het document afsluiten
        workbook.Close()
        workbook = Nothing

        ExcelApp.Quit()
        ExcelApp = Nothing
    End Sub
    Public Shared Sub ImportFromExcel(ByVal db As DatabaseHelper, ByVal filePath As String)
        'Excel openen
        Dim app As New Excel.Application
        'don't show excel
        app.Visible = False
        'Don't display allerts or errors
        app.DisplayAlerts = False

        'Document open
        Dim book As Excel.Workbook = app.Workbooks.Open(filePath)

        'The right worksheet
        Dim sheet As Excel.Worksheet = CType(book.Worksheets("Producten"), Excel.Worksheet)

        'Select the correct range
        Dim range As Excel.Range = sheet.Range("A3")

        'put all the rows in the products list
        Dim prodList As New List(Of Product)
        Do Until range.Value Is Nothing
            'make list of occasions
            Dim occasions As New List(Of Occasion)
            For Each occ As String In range.Offset(0, 4).Value.ToString.Split(CChar(";"))
                occasions.Add(New Occasion(0, occ))
            Next
            'add product
            Dim prod As New Product(0, CStr(range.Value), CStr(range.Offset(0, 1).Value), "Geen", CDec(range.Offset(0, 2).Value), CBool(range.Offset(0, 3).Value), occasions)
            'move one row
            range = range.Offset(1, 0)
            prodList.Add(prod)
        Loop
        'Excel sluiten
        app.Quit()
        app = Nothing

        'Add to database via transactions..
        Dim transaction As OleDbTransaction = db.DBConnection.BeginTransaction()
        Dim prodId As Integer

        Try

            For Each prod As Product In prodList
                db.ExecuteSQL("INSERT INTO Product (ProdNaam,ProdOmschrijving,ProdAfbeelding,ProdPrijs,ProdOnbeschikbaar) VALUES (?,?,?,?,?)",
                               {prod.Naam, prod.Omschrijving, prod.Afbeelding, prod.Prijs, prod.Onbeschikbaar}, transaction)
                'get the id from the last insert with that name (products have an unique name)
                prodId = CInt(db.ExecuteScalar("select ProdId from Product WHERE  ProdNaam = ?", {prod.Naam}, transaction))

                ' add each insert in the transaction..
                For Each occ As Occasion In prod.Occasions
                    'get the occasion id if the Occasion exists.. otherwise create it
                    Dim occId As Integer = 0

                    occId = CInt(db.ExecuteScalar("select GelegenheidID from Gelegenheid WHERE  GelegenheidNaam = ?", {occ.Name}, transaction))

                    If occId = 0 Then
                        'add the new occasion..
                        Occasion.AddOccasion(db, occ, transaction)
                        'Read the occasion id :)
                        occId = CInt(db.ExecuteScalar("select GelegenheidID from Gelegenheid WHERE  GelegenheidNaam = ?", {occ.Name}, transaction))
                    End If
                    Console.WriteLine("Occasion id for peoduct " & prod.Naam & " is " & occId)
                    'insert the occasion for each product..
                    db.ExecuteSQL("INSERT INTO GelegenheidProduct VALUES(?, ? )", {occId, prodId}, transaction)
                Next
            Next
            transaction.Commit()
        Catch ex As Exception
            transaction.Rollback()
        End Try
    End Sub


End Class
